package etf;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class HelloSelenium {

	public static void main(String[] args) {

		System.setProperty("webdriver.gecko.driver", "/WebDriver/bin/geckodriver.exe");
		//火狐的驱动（基于java模拟的浏览器）
		WebDriver driver = new FirefoxDriver();
		try {
			driver.manage().timeouts().implicitlyWait(40, TimeUnit.SECONDS);
			driver.get("https://www.etf.com/etfanalytics/etf-finder/");
			for (int i = 0; i < 235; i++) {
				fetchOnePage(driver);

				WebElement nextPage = driver.findElement(By.id("nextPage"));
				click(nextPage, driver);

			}

		} finally {
            driver.quit();
		}
	}

	private static void fetchOnePage(WebDriver driver) {
		WebElement results = driver.findElement(By.id("results"));

		List<WebElement> tooltips = results.findElement(By.id("table-tabs"))
				.findElements(By.className("Tooltip_default"));
		List<Fund> list = new ArrayList<>();
		List<WebElement> tickerNames = results.findElements(By.className("linkTickerName"));
		for (int i = 0; i < tickerNames.size(); i++) {
			Fund fund = new Fund();
			list.add(fund);

			WebElement ticker = tickerNames.get(i);
			List<WebElement> td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./.."))
					.findElements(By.tagName("td"));
			
			Basic basic = new Basic();
			fund.basic = basic;
			basic.ticker = td.get(0).getText();
			basic.name = td.get(1).getText();
			basic.segment = td.get(2).getText();
			basic.issuer = td.get(3).getText();
			basic.expenseRatio = td.get(4).getText();
			basic.aum = td.get(5).getText();
			String sql = "INSERT INTO th_fund_us_basics " +
					"(ticker,name,segment,issuer,expenseRatio,aum,created_time) " +
					"VALUES " +
					"(?,?,?,?,?,?,?)";

			WebElement tab2 = tooltips.get(1).findElement(By.tagName("span"));
			click(tab2, driver);

			td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./..")).findElements(By.tagName("td"));
			Performance performance = new Performance();
			fund.performance = performance;
			performance.oneM = td.get(2).getText();
			performance.threeM = td.get(3).getText();
			performance.ytd = td.get(4).getText();
			performance.oneY = td.get(5).getText();
			performance.threeY = td.get(6).getText();
			performance.fiveY = td.get(7).getText();
			performance.tenY = td.get(8).getText();
			performance.asOfDate = td.get(9).getText();
			String sql2 = "INSERT INTO th_fund_us_performance " +
					"(ticker,name,oneM,threeM,ytd,oneY,threeY,fiveY,tenY,asOfDate,created_time) " +
					"VALUES " +
					"(?,?,?,?,?,?,?,?,?,?,?)";

			WebElement tab3 = tooltips.get(2).findElement(By.tagName("span"));
			click(tab3, driver);

			td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./..")).findElements(By.tagName("td"));
			Flows flows = new Flows();
			fund.flows = flows;
			flows.oneM = td.get(2).getText();
			flows.threeM = td.get(3).getText();
			flows.ytd = td.get(4).getText();
			flows.oneY = td.get(5).getText();
			flows.threeY = td.get(6).getText();
			flows.asOfDate = td.get(7).getText();
			String sql3 = "INSERT INTO th_fund_us_flows " +
					"(ticker,name,oneM,threeM,ytd,oneY,threeY,asOfDate,created_time) " +
					"VALUES " +
					"(?,?,?,?,?,?,?,?,?)";

			WebElement tab4 = tooltips.get(3).findElement(By.tagName("span"));
			click(tab4, driver);

			td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./..")).findElements(By.tagName("td"));
			Analysis analysis = new Analysis();
			fund.analysis = analysis;
			analysis.analystPick = td.get(2).getText();
			analysis.grade = td.get(3).getText();
			analysis.efficiency = td.get(4).getText();
			analysis.tradability = td.get(5).getText();
			analysis.fit = td.get(6).getText();
			analysis.segment = td.get(7).getText();
			String sql4 = "INSERT INTO th_fund_us_analysis " +
					"(ticker,name,analysispick,grade,efficiency,tradability,fit,segment,created_time) " +
					"VALUES " +
					"(?,?,?,?,?,?,?,?,?)";

			WebElement tab5 = tooltips.get(4).findElement(By.tagName("span"));
			click(tab5, driver);

			td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./..")).findElements(By.tagName("td"));
			Fundamentals fundamentals = new Fundamentals();
			fund.fundamentals = fundamentals;
			fundamentals.dividendYield = td.get(2).getText();
			fundamentals.pe = td.get(3).getText();
			fundamentals.pb = td.get(4).getText();
			fundamentals.duration = td.get(5).getText();
			fundamentals.creditQuality = td.get(6).getText();
			fundamentals.ytm = td.get(7).getText();
			String sql5 = "INSERT INTO th_fund_us_fundamentals " +
					"(ticker,name,dividendyield,pe,pb,duration,creditquality,ytm,created_time) " +
					"VALUES " +
					"(?,?,?,?,?,?,?,?,?)";

			WebElement tab6 = tooltips.get(5).findElement(By.tagName("span"));
			click(tab6, driver);

			td = ticker.findElement(By.xpath("./..")).findElement(By.xpath("./..")).findElements(By.tagName("td"));
			ESG esg = new ESG();
			fund.esg = esg;
			esg.esgRating = td.get(2).getText();
			esg.qualityScore = td.get(3).getText();
			esg.scorePeerRank = td.get(4).getText();
			esg.scoreGlobalRank = td.get(5).getText();
			esg.carbonIntensity = td.get(6).getText();
			esg.sustainableImpactExposure = td.get(7).getText();
			esg.sriScreeningCriteriaExposure = td.get(8).getText();
			String sql6 = "INSERT INTO th_fund_us_esg " +
					"(ticker,name,esgrating,qualityscore,scorepeerrank,scoreglobalrank,carbonintensity,sustainableimpactexposure,sriscreeningcriteriaexposure,created_time)" +
					" VALUES " +
					"(?,?,?,?,?,?,?,?,?,?)";

			WebElement tab1 = tooltips.get(0).findElement(By.tagName("span"));
			click(tab1, driver);

			Connection c = null;
			PreparedStatement stmt = null;
			PreparedStatement stmt2 = null;
			PreparedStatement stmt3 = null;
			PreparedStatement stmt4 = null;
			PreparedStatement stmt5 = null;
			PreparedStatement stmt6 = null;
			try {
				Class.forName("org.postgresql.Driver");
				c = DriverManager
						.getConnection("jdbc:postgresql://XXX/mh_server",
								"postgres", "MoHuoEr2016");

				stmt = c.prepareStatement(sql);
				stmt.setString(1,basic.ticker);
				stmt.setString(2,basic.name);
				stmt.setString(3,basic.segment);
				stmt.setString(4,basic.issuer);
				stmt.setString(5,basic.expenseRatio);
				stmt.setString(6,basic.aum);
				stmt.setTimestamp(7, Timestamp.valueOf(LocalDateTime.now()));

				stmt2 = c.prepareStatement(sql2);
				stmt2.setString(1,basic.ticker);
				stmt2.setString(2,basic.name);
				stmt2.setString(3,performance.oneM);
				stmt2.setString(4,performance.threeM);
				stmt2.setString(5,performance.ytd);
				stmt2.setString(6,performance.oneY);
				stmt2.setString(7,performance.threeY);
				stmt2.setString(8,performance.fiveY);
				stmt2.setString(9,performance.tenY);
				stmt2.setString(10,performance.asOfDate);
				stmt2.setTimestamp(11, Timestamp.valueOf(LocalDateTime.now()));

				stmt3 = c.prepareStatement(sql3);
				stmt3.setString(1,basic.ticker);
				stmt3.setString(2,basic.name);
				stmt3.setString(3,flows.oneM);
				stmt3.setString(4,flows.threeM);
				stmt3.setString(5,flows.ytd);
				stmt3.setString(6,flows.oneY);
				stmt3.setString(7,flows.threeY);
				stmt3.setString(8,flows.asOfDate);
				stmt3.setTimestamp(9, Timestamp.valueOf(LocalDateTime.now()));

				stmt4 = c.prepareStatement(sql4);
				stmt4.setString(1,basic.ticker);
				stmt4.setString(2,basic.name);
				stmt4.setString(3,analysis.analystPick);
				stmt4.setString(4,analysis.grade);
				stmt4.setString(5,analysis.efficiency);
				stmt4.setString(6,analysis.tradability);
				stmt4.setString(7,analysis.fit);
				stmt4.setString(8,analysis.segment);
				stmt4.setTimestamp(9, Timestamp.valueOf(LocalDateTime.now()));

				stmt5 = c.prepareStatement(sql5);
				stmt5.setString(1,basic.ticker);
				stmt5.setString(2,basic.name);
				stmt5.setString(3,fundamentals.dividendYield);
				stmt5.setString(4,fundamentals.pe);
				stmt5.setString(5,fundamentals.pb);
				stmt5.setString(6,fundamentals.duration);
				stmt5.setString(7,fundamentals.creditQuality);
				stmt5.setString(8,fundamentals.ytm);
				stmt5.setTimestamp(9, Timestamp.valueOf(LocalDateTime.now()));

				stmt6 = c.prepareStatement(sql6);
				stmt6.setString(1,basic.ticker);
				stmt6.setString(2,basic.name);
				stmt6.setString(3,esg.esgRating);
				stmt6.setString(4,esg.qualityScore);
				stmt6.setString(5,esg.scorePeerRank);
				stmt6.setString(6,esg.scoreGlobalRank);
				stmt6.setString(7,esg.carbonIntensity);
				stmt6.setString(8,esg.sustainableImpactExposure);
				stmt6.setString(9,esg.sriScreeningCriteriaExposure);
				stmt6.setTimestamp(10, Timestamp.valueOf(LocalDateTime.now()));

				stmt.executeUpdate();
				stmt2.executeUpdate();
				stmt3.executeUpdate();
				stmt4.executeUpdate();
				stmt5.executeUpdate();
				stmt6.executeUpdate();

				stmt.close();
				stmt2.close();
				stmt3.close();
				stmt4.close();
				stmt5.close();
				stmt6.close();
				c.close();

			} catch (Exception e) {
				e.printStackTrace();
				System.err.println(e.getClass().getName()+": "+e.getMessage());
				System.exit(0);
			}
		}
		
		WebElement tab1 = tooltips.get(0).findElement(By.tagName("span"));
		click(tab1, driver);
		
		for (Fund fund : list) {
			System.out.println(fund.basic);
			System.out.println(fund.performance);
			System.out.println(fund.flows);
			System.out.println(fund.analysis);
			System.out.println(fund.fundamentals);
			System.out.println(fund.esg);
			System.out.println();
		}
	}

	private static void click(WebElement element, WebDriver driver) {
		try {
			element.click();
		} catch (Exception e) {
			JavascriptExecutor executor = (JavascriptExecutor) driver;
			executor.executeScript("arguments[0].click();", element);
		}
		try {
			Thread.sleep(10);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
	}
}

class Fund {
	Basic basic;
	Performance performance;
	Flows flows;
	Analysis analysis;
	Fundamentals fundamentals;
	ESG esg;
}

class Basic {
	String ticker;
	String name;
	String segment;
	String issuer;
	String expenseRatio;
	String aum;

	@Override
	public String toString() {
		return "Basic [ticker=" + ticker + ", name=" + name + ", segment=" + segment + ", issuer=" + issuer
				+ ", expenseRatio=" + expenseRatio + ", aum=" + aum + "]";
	}
}

class Performance {
	String oneM;
	String threeM;
	String ytd;
	String oneY;
	String threeY;
	String fiveY;
	String tenY;
	String asOfDate;

	@Override
	public String toString() {
		return "Performance [oneM=" + oneM + ", threeM=" + threeM + ", ytd=" + ytd + ", oneY=" + oneY + ", threeY="
				+ threeY + ", fiveY=" + fiveY + ", tenY=" + tenY + ", asOfDate=" + asOfDate + "]";
	}
}

class Flows{
	String oneM;
	String threeM;
	String ytd;
	String oneY;
	String threeY;
	String asOfDate;
	@Override
	public String toString() {
		return "Flows [oneM=" + oneM + ", threeM=" + threeM + ", ytd=" + ytd + ", oneY=" + oneY + ", threeY=" + threeY
				+ ", asOfDate=" + asOfDate + "]";
	}
}

class Analysis {
	String analystPick;
	String grade;
	String efficiency;
	String tradability;
	String fit;
	String segment;
	@Override
	public String toString() {
		return "Analysis [analystPick=" + analystPick + ", grade=" + grade + ", efficiency=" + efficiency
				+ ", tradability=" + tradability + ", fit=" + fit + ", segment=" + segment + "]";
	}
}

class Fundamentals {
	String dividendYield;
	String pe;
	String pb;
	String duration;
	String creditQuality;
	String ytm;
	@Override
	public String toString() {
		return "Fundamentals [dividendYield=" + dividendYield + ", pe=" + pe + ", pb=" + pb + ", duration=" + duration
				+ ", creditQuality=" + creditQuality + ", ytm=" + ytm + "]";
	}
}

class ESG {
	String esgRating;
	String qualityScore;
	String scorePeerRank;
	String scoreGlobalRank;
	String carbonIntensity;
	String sustainableImpactExposure;
	String sriScreeningCriteriaExposure;
	@Override
	public String toString() {
		return "ESG [esgRating=" + esgRating + ", qualityScore=" + qualityScore + ", scorePeerRank=" + scorePeerRank
				+ ", scoreGlobalRank=" + scoreGlobalRank + ", carbonIntensity=" + carbonIntensity
				+ ", sustainableImpactExposure=" + sustainableImpactExposure + ", sriScreeningCriteriaExposure="
				+ sriScreeningCriteriaExposure + "]";
	}
}

